package epsi.mobile.business;

import java.util.ArrayList;
import java.util.List;

import epsi.mobile.constants.LocalisationConstants;
import epsi.mobile.constants.StoreSQLConstants;
import epsi.mobile.entities.Store;
import epsi.mobile.helpers.DBCreateHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/*
 * This class contains all functions to handle stores
 */
public class StoreProcess 
{
	private static final int VERSION_BDD = 1;
	private static final String NOM_BDD = "StoreLocator.db";
 
	private SQLiteDatabase db;
	private DBCreateHelper dbHelper;
 
	public StoreProcess(Context context)
	{
		dbHelper = new DBCreateHelper(context, NOM_BDD, null, VERSION_BDD);
		this.open();
		dbHelper.createTable(db, StoreSQLConstants.TABLE_CREATEREQUEST_STORE);
	}
 
	public void open()
	{
		db = dbHelper.getWritableDatabase();
	}
 
	public void close()
	{
		db.close();
	}
 
	public SQLiteDatabase getBDD()
	{
		try
		{
			return db;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return null;
	}
	
    /*
     * This function allow to insert a list of store into the database
     */
	public long insertStoreList(List<Store> stores)
	{
		try
		{
			ContentValues values = new ContentValues();
			long ret = 0;
		
			for(int i =0; i< 
					stores.size(); i++) // Bug a corriger
			{
				values.put(StoreSQLConstants.COL_STOREID, stores.get(i).getStoreId());
				values.put(StoreSQLConstants.COL_STORENAME, stores.get(i).getStoreName());
				values.put(StoreSQLConstants.COL_ADRESS, stores.get(i).getAdress());
				values.put(StoreSQLConstants.COL_POSTALCODE, stores.get(i).getPostalCode());
				values.put(StoreSQLConstants.COL_CITY, stores.get(i).getCity());
				values.put(StoreSQLConstants.COL_PHONENUMBER, stores.get(i).getPhoneNumber());
				values.put(StoreSQLConstants.COL_TIMETABLE, stores.get(i).getTimeTable());
				values.put(StoreSQLConstants.COL_FAX, stores.get(i).getFax());
				values.put(StoreSQLConstants.COL_XPOSITION, stores.get(i).getXposition());
				values.put(StoreSQLConstants.COL_YPOSITION, stores.get(i).getYposition());
			
				ret = db.insert(StoreSQLConstants.TABLE_NAME_STORE, null, values);
			}
			return ret;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return 0;
	}
 
	/*
     * This function allows to insert a single store
     */
	public long insertStore(Store store)
	{
		try
		{
			ContentValues values = new ContentValues();
		
			values.put(StoreSQLConstants.COL_STOREID, store.getStoreId());
			values.put(StoreSQLConstants.COL_STORENAME, store.getStoreName());
			values.put(StoreSQLConstants.COL_ADRESS, store.getAdress());
			values.put(StoreSQLConstants.COL_POSTALCODE, store.getPostalCode());
			values.put(StoreSQLConstants.COL_CITY, store.getCity());
			values.put(StoreSQLConstants.COL_PHONENUMBER, store.getPhoneNumber());
			values.put(StoreSQLConstants.COL_TIMETABLE, store.getTimeTable());
			values.put(StoreSQLConstants.COL_FAX, store.getFax());
			values.put(StoreSQLConstants.COL_XPOSITION, store.getXposition());
			values.put(StoreSQLConstants.COL_YPOSITION, store.getYposition());
	
			return db.insert(StoreSQLConstants.TABLE_NAME_STORE, null, values);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return 0;
	}
 
	/*
     * This function allows to update a single store
     */
	public int updateStore(int id, Store store)
	{
		try
		{
			ContentValues values = new ContentValues();
			
			values.put(StoreSQLConstants.COL_STORENAME, store.getStoreName());
			values.put(StoreSQLConstants.COL_ADRESS, store.getAdress());
			values.put(StoreSQLConstants.COL_POSTALCODE, store.getPostalCode());
			values.put(StoreSQLConstants.COL_CITY, store.getCity());
			values.put(StoreSQLConstants.COL_PHONENUMBER, store.getPhoneNumber());
			values.put(StoreSQLConstants.COL_TIMETABLE, store.getTimeTable());
			values.put(StoreSQLConstants.COL_FAX, store.getFax());
			values.put(StoreSQLConstants.COL_XPOSITION, store.getXposition());
			values.put(StoreSQLConstants.COL_YPOSITION, store.getYposition());
			
			return db.update(StoreSQLConstants.TABLE_NAME_STORE, values, StoreSQLConstants.COL_STOREID + " = " +id, null);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return 0;
	}
	
    /*
     * This function allows to update
     */
	public int removeStore()
	{
		try
		{
			return db.delete(StoreSQLConstants.TABLE_NAME_STORE,null, null);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return 0;
	}
 
    /*
     * This function allows to remove a store knowing its id
     */
	public int removeStoreByID(int id)
	{
		try
		{
			return db.delete(StoreSQLConstants.TABLE_NAME_STORE, StoreSQLConstants.COL_STOREID + " = " +id, null);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return 0;
	}
 
    /*
     * This function returns a store knowing id
     */
	public Store getStoreById(String storeId)
	{
		try
		{
			Cursor c = db.query(StoreSQLConstants.TABLE_NAME_STORE, 
					new String[] {StoreSQLConstants.COL_STOREID,
					StoreSQLConstants.COL_STORENAME,
					StoreSQLConstants.COL_ADRESS,
					StoreSQLConstants.COL_POSTALCODE,
					StoreSQLConstants.COL_CITY,
					StoreSQLConstants.COL_PHONENUMBER,
					StoreSQLConstants.COL_TIMETABLE,
					StoreSQLConstants.COL_FAX,
					StoreSQLConstants.COL_XPOSITION,
					StoreSQLConstants.COL_YPOSITION}, 
					StoreSQLConstants.COL_STOREID + " like \"" + storeId +"\"",
					null, null, null, null);
			return cursorToStore(c);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return new Store();
	}
	
    /*
     * This function return a list of store knowing their postal code
     */
	public ArrayList<Store> getStoreByPostalCode(String storePostalCode)
    {
        try
        {
            Cursor c = db.query(StoreSQLConstants.TABLE_NAME_STORE, 
                    new String[] {StoreSQLConstants.COL_STOREID,
                    StoreSQLConstants.COL_STORENAME,
                    StoreSQLConstants.COL_ADRESS,
                    StoreSQLConstants.COL_POSTALCODE,
                    StoreSQLConstants.COL_CITY,
                    StoreSQLConstants.COL_PHONENUMBER,
                    StoreSQLConstants.COL_TIMETABLE,
                    StoreSQLConstants.COL_FAX,
                    StoreSQLConstants.COL_XPOSITION,
                    StoreSQLConstants.COL_YPOSITION}, 
                    StoreSQLConstants.COL_POSTALCODE + " like \"" + storePostalCode +"%\"",
                    null, null, null, null);
            return cursorToStores(c);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        } 
        return new ArrayList<Store>();
    }
    
    /*
     * This function returns the list of store whose contains the given text
     */
	public ArrayList<Store> getStoreByText(String text)
    {
        try
        {
            Cursor c = db.query(StoreSQLConstants.TABLE_NAME_STORE, 
                    new String[] 
                    {
                        StoreSQLConstants.COL_STOREID,
                        StoreSQLConstants.COL_STORENAME,
                        StoreSQLConstants.COL_ADRESS,
                        StoreSQLConstants.COL_POSTALCODE,
                        StoreSQLConstants.COL_CITY,
                        StoreSQLConstants.COL_PHONENUMBER,
                        StoreSQLConstants.COL_TIMETABLE,
                        StoreSQLConstants.COL_FAX,
                        StoreSQLConstants.COL_XPOSITION,
                        StoreSQLConstants.COL_YPOSITION
                    }, 
                    StoreSQLConstants.COL_STORENAME + " like \"%" + text +"%\" OR "
                  + StoreSQLConstants.COL_ADRESS + " like \"%" + text +"%\" OR "
                  + StoreSQLConstants.COL_CITY + " like \"%" + text +"%\"",
                    null, null, null, null);
            return cursorToStores(c);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        } 
        return new ArrayList<Store>();
    }

    /*
     * This function return a Store knowing its name
     */
	public Store getStoreByName(String storeName)
	{
		try
		{
			Cursor c = db.query(StoreSQLConstants.TABLE_NAME_STORE, 
					new String[] {StoreSQLConstants.COL_STOREID,
					StoreSQLConstants.COL_STORENAME,
					StoreSQLConstants.COL_ADRESS,
					StoreSQLConstants.COL_POSTALCODE,
					StoreSQLConstants.COL_CITY,
					StoreSQLConstants.COL_PHONENUMBER,
					StoreSQLConstants.COL_TIMETABLE,
					StoreSQLConstants.COL_FAX,
					StoreSQLConstants.COL_XPOSITION,
					StoreSQLConstants.COL_YPOSITION}, 
					StoreSQLConstants.COL_STORENAME + " like \"" + storeName +"\"",
					null, null, null, null);
			return cursorToStore(c);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return new Store();
	}
	
    /*
     * This function returns the list of all the stores
     */
	public ArrayList<Store> getAllStores()
	{
		try
		{
			Cursor c = db.query(
					StoreSQLConstants.TABLE_NAME_STORE, 
					new String[] {StoreSQLConstants.COL_STOREID,
					StoreSQLConstants.COL_STORENAME,
					StoreSQLConstants.COL_ADRESS,
					StoreSQLConstants.COL_POSTALCODE,
					StoreSQLConstants.COL_CITY,
					StoreSQLConstants.COL_PHONENUMBER,
					StoreSQLConstants.COL_TIMETABLE,
					StoreSQLConstants.COL_FAX,
					StoreSQLConstants.COL_XPOSITION,
					StoreSQLConstants.COL_YPOSITION}, 
					null,null, null, null, null);
			return cursorToStores(c);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return new ArrayList<Store>();
	}
 
    /*
     * This function returns the store pointed by the given cursor
     */
	private Store cursorToStore(Cursor c)
	{
		try
		{
			if (c.getCount() <= 0)
				return new Store();
	 
			c.moveToFirst();
	
			Store store = new Store();
			
			store.setStoreId(c.getString(StoreSQLConstants.NUM_COL_STOREID));
			store.setStoreName(c.getString(StoreSQLConstants.NUM_COL_STORENAME));
			store.setAdress(c.getString(StoreSQLConstants.NUM_COL_ADRESS));
			store.setPostalCode(c.getInt(StoreSQLConstants.NUM_COL_POSTALCODE));
			store.setCity(c.getString(StoreSQLConstants.NUM_COL_CITY));
			store.setPhoneNumber(c.getString(StoreSQLConstants.NUM_COL_PHONENUMBER));
			store.setTimeTable(c.getString(StoreSQLConstants.NUM_COL_TIMETABLE));
			store.setFax(c.getString(StoreSQLConstants.NUM_COL_FAX));
			store.setXposition(c.getDouble(StoreSQLConstants.NUM_COL_XPOSITION));
			store.setYposition(c.getDouble(StoreSQLConstants.NUM_COL_YPOSITION));
			c.close();
	
			return store;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return new Store();
	}
	
	/*
     * This function returns the list of store pointed by the given cursor
     */
	private ArrayList<Store> cursorToStores(Cursor c)
	{
		try
		{
			if (c.getCount() <= 0)
				return new ArrayList<Store>();
	 
			ArrayList<Store> als = new ArrayList<Store>();
			c.moveToFirst();
	
			Store store;
			for(int i=0; i<c.getCount();i++,c.moveToNext())
			{
				store = new Store();
				
				store.setStoreId(c.getString(StoreSQLConstants.NUM_COL_STOREID));
				store.setStoreName(c.getString(StoreSQLConstants.NUM_COL_STORENAME));
				store.setAdress(c.getString(StoreSQLConstants.NUM_COL_ADRESS));
				store.setPostalCode(c.getInt(StoreSQLConstants.NUM_COL_POSTALCODE));
				store.setCity(c.getString(StoreSQLConstants.NUM_COL_CITY));
				store.setPhoneNumber(c.getString(StoreSQLConstants.NUM_COL_PHONENUMBER));
				store.setTimeTable(c.getString(StoreSQLConstants.NUM_COL_TIMETABLE));
				store.setFax(c.getString(StoreSQLConstants.NUM_COL_FAX));
				store.setXposition(c.getDouble(StoreSQLConstants.NUM_COL_XPOSITION));
				store.setYposition(c.getDouble(StoreSQLConstants.NUM_COL_YPOSITION));
				
				als.add(store);
			}
			c.close();
	
			return als;
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		} 
		return new ArrayList<Store>();
	}
	
	/*
     * This function returns the list of store near the given position
     */
	public ArrayList<Store> getNearbyStores(double currentLatitude, double currentLongitude)
	{
		ArrayList<Store> nearbyStores = new ArrayList<Store>();
		ArrayList<Store> ret = new ArrayList<Store>();
		nearbyStores = this.getAllStores();
		
		for(int i=0;i<nearbyStores.size();i++)
		{
			float distance = LocalisationConstants.distFrom(currentLatitude, currentLongitude, nearbyStores.get(i).getXposition(), nearbyStores.get(i).getYposition()); 
			
			if(distance <= LocalisationConstants.DISTANCE_FOR_NEARBY_STORES)
			{
				ret.add(nearbyStores.get(i));
			}
		}
		return ret;
	}
}
